DROP TRIGGER UPDATE_USER_ID_TRI;
DROP SEQUENCE USER_ID_SEQ;
DROP TABLE USERS;

--创建表
CREATE TABLE USERS(
  USER_ID INT PRIMARY KEY,
  USER_NAME VARCHAR2(20) NOT NULL,
  SEX CHAR(1BYTE) NOT NULL CHECK (SEX=0 OR SEX=1),
  BIRTHDATE DATE,
  ADDRESS NVARCHAR2(200)
);

--创建序列
CREATE SEQUENCE USER_ID_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCACHE;

--创建触发器
CREATE TRIGGER UPDATE_USER_ID_TRI BEFORE
INSERT ON USERS FOR EACH ROW WHEN(NEW.USER_ID IS NULL)
BEGIN
SELECT USER_ID_SEQ.NEXTVAL INTO:NEW.USER_ID FROM DUAL;
END;
